﻿using Common.Logging;
using Quartz;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wicresoft.UnifyShow.Business;
using Wicresoft.UnifyShow.BusinessImpl;

namespace Wicresoft.UnifyShow.PlanServices
{
    public class CreateTask : Basic, IJob
    {
        string insertTable = @"
                insert into task(jobno,jobid,cycleid,taskname,TaskDescription,TaskDetail,status,location,jobtype,begindate,enddate,OwnerID,createuserid,updateuserid,createtime,updatetime,overflag,remark)  
                            ";
        public void Execute(IJobExecutionContext context)
        {
            Logger.Log("start Create Task");
            try
            {
                ModelHelper<TaskModel>.CreateModel_By_DirectSql(insertTable + sqlCycleType1, oda);
            }
            catch (Exception ex)
            {
                Logger.Error("一次:", ex);
            }
            try
            {
                ModelHelper<TaskModel>.CreateModel_By_DirectSql(insertTable + sqlCycleType20, oda);
            }
            catch (Exception ex)
            {
                Logger.Error("每天:", ex);
            }
            try
            {
                ModelHelper<TaskModel>.CreateModel_By_DirectSql(insertTable + sqlCycleType20, oda);
            }
            catch (Exception ex)
            {
                Logger.Error("每天(周六日除外):", ex);
            }
            try
            {
                ModelHelper<TaskModel>.CreateModel_By_DirectSql(insertTable + sqlCycleType3, oda);
            }
            catch (Exception ex)
            {
                Logger.Error("每周:", ex);
            }
            try
            {
                ModelHelper<TaskModel>.CreateModel_By_DirectSql(insertTable + sqlCycleType4, oda);
            }
            catch (Exception ex)
            {
                Logger.Error("每月:", ex);
            }

            try
            {
                ModelHelper<TaskModel>.CreateModel_By_DirectSql(@"update task set OverFlag=1
                                                                    where Status=1 and OverFlag=0 and  to_char(enddate+1,'YYYY/MM/DD')<= to_char(sysdate,'YYYY/MM/DD')
                                                                ", oda);
            }
            catch(Exception ex)
            {
                Logger.Error("任务过期:", ex);
            }
        }

        #region sql
        #region sql  1
        ///一次
        string sqlCycleType1 = @"
                                    select to_char(sysdate,'YYYYMMDD')||'_'||a.ID||'_'||b.id JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                    ,begindate
                                    ,enddate
                                    ,TaskUserID OwnerID
                                    ,JobOwnerID CreateUserID
                                    ,JobOwnerID UpdateUserID
                                    ,sysdate CreateTime
                                    ,sysdate UpdateTime
                                    ,0 OverFlag
                                    ,'' Remark
                                    from job_x a
                                    left join cycle b on a.id=b.jobid
                                 left join PlanTaskScope d on b.id=d.CycleID
                                   where b.PlanType=1 
                                    and to_char(b.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD')
                                    and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                ";
        #endregion
        #region sql  2
        ///每天  周六日有效
        string sqlCycleType20 = @"
                                    select to_char(sysdate,'YYYYMMDD')||'_'||a.ID||'_'||b.id JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD') enddate
                                    ,TaskUserID OwnerID
                                    ,JobOwnerID CreateUserID
                                    ,JobOwnerID UpdateUserID
                                    ,sysdate CreateTime
                                    ,sysdate UpdateTime
                                    ,0 OverFlag
                                    ,'' Remark
                                    from job_x a
                                    left join cycle b on a.id=b.jobid
                                left join PlanTaskScope d on b.id=d.CycleID
                                    where b.PlanType=2 and a.Invalid=1 and HolidayInValid =0
                                    and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') and (b.EndDate is null or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                    and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                    ";
        ///每天  周六日无效
        string sqlCycleType21 = @"
                                    select to_char(sysdate,'YYYYMMDD')||'_'||a.ID||'_'||b.id JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD') enddate
                                    ,TaskUserID OwnerID
                                    ,JobOwnerID CreateUserID
                                    ,JobOwnerID UpdateUserID
                                    ,sysdate CreateTime
                                    ,sysdate UpdateTime
                                    ,0 OverFlag
                                    ,'' Remark
                                    from job_x a
                                    left join cycle b on a.id=b.jobid
                                    left join PlanTaskScope d on b.id=d.CycleID
                                    where b.PlanType=2 and a.Invalid=1 and HolidayInValid =1 and to_char(sysdate - 1,'d') not in (6,7)
                                    and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') and (b.EndDate is null or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                    and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'));
                                    ";
        #endregion
        #region sql  3
        ///每周
        string sqlCycleType3 = @"
                                select to_char(sysdate,'YYYYMMDD')||'_'||a.ID||'_'||b.id JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                ,to_date(to_char(sysdate+b.length,'YYYY/MM/DD'),'YYYY/MM/DD') enddate
                                ,TaskUserID OwnerID
                                ,JobOwnerID CreateUserID
                                ,JobOwnerID UpdateUserID
                                ,sysdate CreateTime
                                ,sysdate UpdateTime
                                ,0 OverFlag
                                ,'' Remark
                                from job_x a
                                left join cycle b on a.id=b.jobid
                                 left join PlanTaskScope d on b.id=d.CycleID
                                where b.PlanType=3 and a.Invalid=1 and  to_char(sysdate - 1,'d')=b.day
                                and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') and (b.EndDate is null or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))                                ";
        #endregion
        #region sql  4
        ///每月
        string sqlCycleType4 = @"
                                select to_char(sysdate,'YYYYMMDD')||'_'||a.ID||'_'||b.id JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                 ,case when to_char(sysdate+b.length,'YYYY/MM/DD')>to_char(add_months(sysdate,1)-1,'YYYY/MM/DD') then add_months(sysdate,1)-1 
                                  else to_date(to_char(sysdate+b.length,'YYYY/MM/DD'),'YYYY/MM/DD') end enddate
                                 ,TaskUserID OwnerID
                                ,JobOwnerID CreateUserID
                                ,JobOwnerID UpdateUserID
                                ,sysdate CreateTime
                                ,sysdate UpdateTime
                                ,0 OverFlag
                                ,'' Remark
                                from job_x a
                                left join cycle b on a.id=b.jobid
                                left join PlanTaskScope d on b.id=d.CycleID
                                where b.PlanType=4 and a.Invalid=1 and  to_char(sysdate - 1,'d')=b.day and to_char(sysdate - 1,'m')=b.month
                                and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') and (b.EndDate is null or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                ";
        #endregion
        #endregion
    }
}
